Since 1998 David Cross has built up a thriving business in Lancaster and the surrounding area supplying financial, computing and IT services to business and leisure clients, including shops, small businesses, hotels, restaurants etc.

The company has a number of income streams but the one David has asked you to investigate relates to providing IT and computer support, usually remotely over the telephone but sometimes on-site.

David currently offers 3 product ‘packages’.
These packages have basically emerged over time – more by accident than any hard analysis!

Premium service (A) Clients pay a single yearly fee (currently £400) and then all telephone and onsite support is included, with no limits on the number of calls or visits. All calls made by the client to David are free.

Standard service (B) Clients pay a quarterly fee, £45, every 3 months. The first ten calls a quarter are free, after that they are charged at £1.40/minute*. On-site visits are not included but can be arranged and charged at fixed rate of £40 per visit, regardless of the time involved.

Basic service (C) Clients don’t pay any fixed fee but are simply charged at £1.40/minute* for telephone support. No on-site visits are included or offered by David.

David currently has 39 premium clients, 131 standard and 65 basic. The clients have also been organised into four regions; north, east, south and west.

He has called you in to help analyse the current situation and advise on various pricing and re-structuring ideas.

To support this analysis, he has collected data for the last quarter (3 months), including the number of calls, the duration of the calls (in minutes and seconds, to the nearest 10 seconds) and the number of on-site visits.

* only complete, whole minutes of a telephone call are charged

Modelling Tasks [marks in brackets]

1. Design and construct an interactive, flexible model which enables David to investigate the pattern(s) of customer activity per quarter and investigate the various options open to him.

Include a ‘user’ sheet which briefly explains the key aspects of your model, and instructs or guides the user as to where to find the answers to the research questions below. High marks will go to models that are well designed and structured, have clarity, flexibility and are intuitive to use – and are not overly complex. [40]

2. Financially, David currently receives 60% of the phone charge paid by the customer as direct income. In other words, if the customer is charged £1.40/minute then David receives 84 pence as income.

David negotiates this percentage with the telephone company, so this is may change in the future.
For any phone call that David provides free of charge to his customers (Premium and Standard users only) he currently pays 40 pence/minute* to the telephone company.

For the three types of customer (A, B and C) calculate and briefly comment on:

i. the average and maximum number of calls
ii. the average and maximum call length, in minutes and seconds
iii. the total income (including the 40p/min payment David makes to the telephone company)
iv. the net income per customer, and comment on which package performs the ‘best’ [20]

* only complete, whole minutes of a telephone call are charged
3. Using the current call data as a guide, adapt your model to simulate the number and duration of the telephone calls for the 235 customers. Based on a quarterly time-frame (3 months) and using a suitable number of simulations provide the following outputs:

i. the distribution for the average number of calls, for the three customer types
ii. the distribution for the average call length, for the three customer types
iii. the distribution of the quarterly income, the three types of customers [20]
4. New Pricing: David is considering simplifying the business so that all customers are in the same pricing package (D). His idea is to do the following:

The customer pays a fixed monthly fee, and all calls up to, say, 8 minutes in duration are free. Any calls longer than 8 minutes are charged at £1.60/minute (for just the time over 8 minutes).

Using the same costing conditions as above (60% income, 40p/min cost to David for the free calls and £40 per callout) can you advise on a suitable monthly fee? David has suggested £12/month.

Perhaps show how quarterly revenue might be sensitive to different monthly fees,
What assumptions have you made when analysing this idea, and how might you improve the analysis to make it more realistic/accurate – perhaps using some of the ideas explored in task 3? [20]
Deliverable

Students are required to submit a single Excel 2010 model, uploaded to Moodle. The model should not be password protected or contain external links.

The model should be uploaded by Tuesday 19th January 2016. All standard Management School rules and University regulations apply for coursework submission and plagiarism.

All students will receive a unique dataset. Use of any other dataset will constitute plagiarism.